package cn.life.business.inspect.dao.impl;

import cn.life.business.inspect.dao.InspectDao;
import cn.life.business.inspect.entity.po.Inspect;
import cn.life.business.inspect.entity.vo.InspectVo;
import cn.life.common.entity.vo.Pagination;
import cn.life.common.support.jdbc.AbstractSupportDao;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 王振宇
 * @Description:
 * @Date 2018-07-26 10:43:07
 */
@Repository
public class InspectDaoImpl  extends AbstractSupportDao implements InspectDao {

    @Override
    public int addInspect(Inspect inspect) {
        String sql="insert into factory_inspect(team_name,team_desc,person_liable_id,person_liable_name,person_liable_phone,inspect_start_result,inspect_end_result,inspect_state,file_url," +
                "opr_user_id,opr_user_name,opr_date,project_id,project_name,end_date,status) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return update(sql,inspect.getTeamName(),inspect.getTeamDesc(),inspect.getPersonLiableId(),inspect.getPersonLiableName(),
                inspect.getPersonLiablePhone(),inspect.getInspectStartResult(),inspect.getInspectEndResult(),
                inspect.getInspectState(),inspect.getFileUrl(),inspect.getOprUserId(),inspect.getOprUserName(),
                inspect.getOprDate(),inspect.getProjectId(),inspect.getProjectName(),inspect.getEndDate(),inspect.getStatus());
    }

    @Override
    public int deleteInspect(Inspect inspect) {
        String sql= "delete from factory_inspect where inspect_id=? ";
        return update(sql,inspect.getInspectId());
    }

    @Override
    public int updateInspect(Inspect inspect) {
        String sql= "update factory_inspect set inspect_end_result=?,inspect_end_state=1,end_photo=?,opr_date = now() where inspect_id=? ";
        return update(sql,inspect.getInspectEndResult(),inspect.getEndPhoto(),inspect.getInspectId());
    }

    @Override
    public Pagination<InspectVo> selectInspect(InspectVo inspectVo) {
        StringBuffer stringBuffer=new StringBuffer("select *,case inspect_end_state = 0 and now() > end_date when true then 1 else 0 end as inspectStatus from factory_inspect where 1=1");
        if(inspectVo.getProjectId() != null && !inspectVo.getProjectId().equals("")){
            stringBuffer.append(" and project_id = " + inspectVo.getProjectId() + " ");
        }
        if(inspectVo.getInspectState() != 0){
            stringBuffer.append(" and inspect_state = " + inspectVo.getInspectState());
        }
        if ((!"".equals(inspectVo.getTeamName())&&inspectVo.getTeamName()!=null)||(!"".equals(inspectVo.getPersonLiableName())&&inspectVo.getPersonLiableName()!=null)){
            stringBuffer.append(" and (team_name like'%"+inspectVo.getTeamName()+"%' or person_liable_name like '%"+inspectVo.getPersonLiableName()+"%') ");
        }
        if(inspectVo.getStatusStr() != null && inspectVo.getStatusStr().equals("my")){
            stringBuffer.append(" and opr_user_id = " + inspectVo.getOprUserId());
        }
        stringBuffer.append(" ORDER BY opr_date DESC");

        return queryMyForListByPageEx(stringBuffer.toString(),InspectVo.class,inspectVo.getPageIndex(),inspectVo.getPageSize());
    }

    @Override
    public InspectVo getInspect(InspectVo inspectVo) {
        String sql= "select * from factory_inspect where inspect_id=? ";
        return queryForObject(sql,InspectVo.class,inspectVo.getInspectId());
    }

    @Override
    public Pagination<InspectVo> statistics(InspectVo inspectVo) {
        String sql = "SELECT a.person_liable_name,a.person_liable_phone,round((SELECT count(1) FROM factory_inspect b WHERE b.inspect_end_state = 1 AND  b.project_id = ? and  b.person_liable_phone= a.person_liable_phone)/(SELECT count(1) FROM factory_inspect c WHERE c.project_id = ? and c.person_liable_phone  = a.person_liable_phone),2) AS qualificatioRate FROM factory_inspect a WHERE a.project_id = ? GROUP BY a.person_liable_phone ";
        return queryForListByPageEx(sql,InspectVo.class,inspectVo.getPageIndex(),inspectVo.getPageSize(),inspectVo.getProjectId(),inspectVo.getProjectId(),inspectVo.getProjectId());
    }

    @Override
    public Pagination<InspectVo> statisticsDetails(InspectVo inspectVo) {
        String sql= "SELECT * from factory_inspect WHERE person_liable_phone=?";
        return queryForListByPageEx(sql,InspectVo.class,inspectVo.getPageIndex(),inspectVo.getPageSize(),inspectVo.getPersonLiablePhone());
    }

    @Override
    public List<InspectVo> statisticsExcel(InspectVo inspectVo) {
        String sql = "SELECT a.person_liable_name,a.person_liable_phone,round((SELECT count(1) FROM factory_inspect b WHERE b.inspect_end_state = 1 AND  b.project_id = ? and  b.person_liable_phone= a.person_liable_phone)/(SELECT count(1) FROM factory_inspect c WHERE c.project_id = ? and c.person_liable_phone  = a.person_liable_phone),2) AS qualificatioRate FROM factory_inspect a WHERE a.project_id = ? GROUP BY a.person_liable_phone ";
        return queryForList(sql,InspectVo.class,inspectVo.getProjectId(),inspectVo.getProjectId(),inspectVo.getProjectId());
    }

    @Override
    public List<Map<String,Object>> queryInspectListByMessage() {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String nowDay = dateFormat.format(new Date());
        String sql = "select a.opr_user_id as userId,b.jg_push_mark as jg,b.hw_push_mark as hw,b.ios_push_mark as ios from factory_inspect a inner join user_info b on a.opr_user_id = b.user_id where a.end_date like '%" + nowDay + "%' and inspect_end_state = 0";
        return queryForList(sql);
    }


}
